package com.server.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.server.database.SQLCreator;
import com.server.date.DateType;
import com.server.date.DateUtil;
import com.server.service.ICommonDataService;
import com.server.web.DataVector;
import com.server.web.RequestInput;

@Service("CommonDataService")
public class CommonDataService implements ICommonDataService {
	@Resource
	private JdbcTemplate jdbcTemplate;

	/**
	 * 公共方法获取列表
	 * 
	 * @param table
	 * @param _whereStr
	 * @param sortStr
	 * @param requestInput
	 * @return
	 * @author ServerZhang
	 * @date 2016年5月13日
	 */
	@Override
	public DataVector<Map<String, Object>> getListData(String table,
			String _whereStr, String sortStr, RequestInput requestInput) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(_whereStr)) {
			whereStr.append(_whereStr);
		}
		Integer pageIndex = 0;
		Integer pageSize = Integer.MAX_VALUE;
		if (requestInput != null) {
			if (requestInput.getWhereStr() != null
					&& requestInput.getWhereStr().length() > 0) {
				if (!StringUtils.isEmpty(_whereStr)) {
					whereStr.append(" AND ");
				}
				whereStr.append("(").append(requestInput.getWhereStr())
						.append(")");
			}
			if (requestInput.getSortStr() != null
					&& requestInput.getSortStr().length() > 0) {
				sortStr = requestInput.getSortStr();
			}
			pageIndex = requestInput.getPageIndex();
			pageSize = requestInput.getPageSize();
		}
		String sqlCount = SQLCreator.getSqlCount(table, whereStr.toString(),
				null, null);
		String sql = SQLCreator.getSql_Mysql(pageIndex, pageSize, table,
				whereStr.toString(), sortStr, null, null);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		Integer count = jdbcTemplate.queryForObject(sqlCount, Integer.class);
		return new DataVector<Map<String, Object>>(list, count);
	}

	/**
	 * 公共方法获取表单信息
	 * 
	 * @param tableName
	 * @param fieldName
	 * @param refNo
	 * @param whereStr
	 * @param sortStr
	 * @return
	 * @author ServerZhang
	 * @date 2016年5月13日
	 */
	@Override
	public Map<String, Object> getFormData(String tableName, String fieldName,
			String refNo, String whereStr, String sortStr) {
		Map<String, Object> map = new HashMap<String, Object>();
		if (tableName != null) {
			StringBuffer sqlBuffer = new StringBuffer("Select * from ");
			sqlBuffer.append(tableName).append(" where valid=1");
			if (!StringUtils.isEmpty(whereStr)) {
				sqlBuffer.append(" AND ").append(whereStr);
			}
			if (!StringUtils.isEmpty(fieldName) && !StringUtils.isEmpty(refNo)) {
				sqlBuffer.append(" AND ").append(fieldName).append("='")
						.append(refNo).append("'");
			}
			if (!StringUtils.isEmpty(sortStr)) {
				sqlBuffer.append(" Order by ").append(sortStr);
			}
			List<Map<String, Object>> list = jdbcTemplate
					.queryForList(sqlBuffer.toString());
			if (list.size() > 0) {
				map = list.get(0);
			}
		}
		return map;
	}

	/**
	 * 获取某个字段的唯一List
	 * 
	 * @param tableName
	 * @param fieldStr
	 * @param whereStr
	 * @param sortStr
	 * @return
	 * @author ServerZhang
	 * @date 2016年5月13日
	 */
	@Override
	public List<Map<String, Object>> getFieldList(String tableName,
			String fieldStr, String whereStr, String sortStr) {
		if (!StringUtils.isEmpty(tableName)) {
			StringBuffer whereBuffer = new StringBuffer(fieldStr
					+ " is not null");
			if (!StringUtils.isEmpty(whereStr)) {
				whereBuffer.append(" AND ").append(whereStr);
			}
			StringBuffer sqlBuffer = new StringBuffer("Select distinct ");
			sqlBuffer.append(fieldStr).append(" from ").append(tableName)
					.append(" where ").append(whereBuffer).append(" order by ")
					.append(sortStr);
			List<Map<String, Object>> list = jdbcTemplate
					.queryForList(sqlBuffer.toString());
			return list;
		} else {
			return new ArrayList<Map<String, Object>>();
		}
	}

	/**
	 * add
	 * @param operator
	 * @param tableName
	 * @param inputMap
	 * @return
	 * @author ServerZhang
	 * @date 2016年7月13日
	 */
	@Override
	public Boolean add(String operator, String tableName,
			Map<String, Object> inputMap) {
		Boolean isSuccess = true;
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(operator) 
				|| inputMap == null || inputMap.size() <= 0) {
			return false;
		}
		StringBuffer fieldBuffer = new StringBuffer("creater,createTime");
		StringBuffer valueBuffer = new StringBuffer();
		String dateStr = DateUtil.getDateStr(new Date(),DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		valueBuffer.append("'").append(operator).append("','")
				.append(dateStr).append("'");
		Set<String> keySet = inputMap.keySet();
		for (String key : keySet) {
			if ("creater".equals(key) || "createTime".equals(key)) {
				continue;
			}
			Object valueObj = inputMap.get(key);
			if (valueObj == null || StringUtils.isEmpty(valueObj.toString())) {
				continue;
			}
			fieldBuffer.append(",");
			fieldBuffer.append(key);
			valueBuffer.append(",");
			String realValue = valueObj.toString().replaceAll("'", "\\\\'");
			valueBuffer.append("'").append(realValue).append("'");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("insert into ").append(tableName).append("(")
				.append(fieldBuffer).append(")").append(" values(")
				.append(valueBuffer).append(")");
		try {
			int chageCount = jdbcTemplate.update(sqlBuffer.toString());
			if (chageCount < 1) {
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}
	/**
	 * delete
	 * @param tableName
	 * @param field
	 * @param value
	 * @return
	 * @author ServerZhang
	 * @date 2016年7月13日
	 */
	@Override
	public Boolean delete(String tableName, String field, 
			String value) {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(field) 
				||StringUtils.isEmpty(value)) {
			return false;
		}
		Boolean isSuccess = true;
		StringBuffer sqlBuffer = new StringBuffer("Delete from ");
		sqlBuffer.append(tableName).append(" where ").append(field)
			.append("='").append(value).append("'");
		try {
			int chageCount = jdbcTemplate.update(sqlBuffer.toString());
			if (chageCount < 1) {
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}
	@Override
	public Boolean update(String operator, String tableName, 
			String field, String value,
			Map<String, Object> inputMap) {
		Boolean isSuccess = true; 
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(operator) 
				|| StringUtils.isEmpty(field) || StringUtils.isEmpty(value)
				|| inputMap == null || inputMap.size() <= 0) {
			return false;
		}
		StringBuffer updateStr = new StringBuffer("Update ");
		updateStr.append(tableName).append(" set ");
		String dateStr = DateUtil.getDateStr(new Date(),DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		updateStr.append("mender='").append(operator).append("',updateTime='")
			.append(dateStr).append("'");
		Set<String> keySet = inputMap.keySet();
		for(String key : keySet){
			if ("mender".equals(key) || "updateTime".equals(key)) {
				continue;
			}
			Object valueObj = inputMap.get(key);
			if (valueObj == null || StringUtils.isEmpty(valueObj.toString())) {
				continue;
			}
			updateStr.append(",");
			String realValue = valueObj.toString().replaceAll("'", "\\\\'");
			updateStr.append(key).append("='").append(realValue).append("'");
		}
		updateStr.append(" where ").append(field).append("='").append(value).append("'");
		try {
			int chageCount = jdbcTemplate.update(updateStr.toString());
			if (chageCount < 1) {
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}
	
	 /**
	 * 所有国家
	 * @return
	 * @author ServerZhang
	 * @date 2016年8月11日
	 */
	@Override
	public List<Map<String, Object>> countryData(){
		String sql = "Select * from education.tcountry where level=3";
		return jdbcTemplate.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> classData() {
		String sql = "Select * from education.resource_class";
		return jdbcTemplate.queryForList(sql);
	}

	@Override
	public Map<String, Object> continent(String name) {
		String sql = "select * from education.tcountry where id in (select pid from education.tcountry where zh_name='%s')";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(String.format(sql, name));
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}

	@Override
	public List<Map<String, Object>> provinceData() {
		String sql = "select * from education.city where style=0 and parentid=0";
		return jdbcTemplate.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> cityData(String name) {
		String sql = "select * from education.city where parentid=(select linkageid from education.city where name='%s')";
		return jdbcTemplate.queryForList(String.format(sql, name));
	}
	
	/*public static void main(String[] args){
		String dateStr = DateUtil.getDateStr(new Date(),DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		System.out.println(dateStr);
		String string = "He hasn't come yet";
		String newStr = string.replaceAll("'", "\\\\'");
		System.out.println(newStr);
	}*/

	
}
